# coding: utf-8
from datetime import datetime
from sqlalchemy import Column, String, Integer, DateTime, Text
from app.extensions import db


class SjmlConversations(db.Model):
    __tablename__ = 'sjml_conversations'

    id = Column(String(36), primary_key=True, comment='会话ID')
    name = Column(String(255), nullable=False, comment='会话名称')
    status = Column(Integer, nullable=False, comment='状态')
    create_at = Column(DateTime, default=datetime.now, nullable=False, comment='创建时间')
    update_at = Column(DateTime, nullable=True, comment='更新时间')
    start_at = Column(DateTime, nullable=True, comment='开始时间')
    end_at = Column(DateTime, nullable=True, comment='结束时间')
    user_id = Column(String(36), nullable=False, comment='用户ID')
    is_delete = Column(Integer, nullable=True, comment='是否删除')

    def to_dict(self):
        return {
            'id': self.id if self.id is not None else "",
            'name': self.name if self.name is not None else "",
            'status': self.status if self.status is not None else 10,
            'create_at': self.create_at.strftime('%Y-%m-%d %H:%M:%S') if self.create_at else "",
            'update_at': self.update_at.strftime('%Y-%m-%d %H:%M:%S') if self.update_at else "",
            'start_at': self.start_at.strftime('%Y-%m-%d %H:%M:%S') if self.start_at else "",
            'end_at': self.end_at.strftime('%Y-%m-%d %H:%M:%S') if self.end_at else "",
            'user_id': self.user_id if self.user_id is not None else "",
            # 'is_delete': self.is_delete if self.is_delete is not None else 10
        }
    
    @classmethod
    def get_by_id(cls, _id: str):
        """根据ID获取会话信息"""
        return cls.query.get(_id)
    
    @classmethod
    def get_list(cls, page=1, per_page=10, filters=None):
        """
        获取会话列表，支持分页和过滤
        
        Args:
            page: 当前页码，默认为1
            per_page: 每页记录数，默认为10
            filters: 过滤条件，字典格式
        
        Returns:
            tuple: (会话列表, 分页对象)
        """
        query = db.session.query(cls)
        
        # 应用过滤条件
        if filters:
            for key, value in filters.items():
                if hasattr(cls, key) and value is not None:
                    query = query.filter(getattr(cls, key) == value)
        
        # 执行分页查询
        pagination = query.order_by(cls.create_at.desc()).paginate(
            page=page, 
            per_page=per_page, 
            error_out=False
        )
        
        return pagination.items, pagination
    
    @classmethod
    def get_by_user_id(cls, user_id: str):
        """根据用户ID获取会话列表"""
        return db.session.query(cls).filter(cls.user_id == user_id).order_by(cls.create_at.desc()).all()
    
    @classmethod
    def get_by_status(cls, status: int):
        """根据状态获取会话列表"""
        return db.session.query(cls).filter(cls.status == status).order_by(cls.create_at.desc()).all()


class SjmlRecords(db.Model):
    __tablename__ = 'sjml_records'

    id = Column(String(36), primary_key=True, comment='记录ID')
    query = Column(Text, nullable=False, comment='查询内容')
    execute_datas = Column(Text, nullable=True, comment='执行数据')
    conversation_id = Column(String(36), nullable=False, comment='会话ID')
    create_at = Column(DateTime, default=datetime.now, nullable=False, comment='创建时间')
    update_at = Column(DateTime, nullable=True, comment='更新时间')
    status = Column(Integer, nullable=False, comment='状态')
    user_id = Column(String(36), nullable=False, comment='用户ID')
    data_conf_id = Column(String(36), nullable=False, comment='数据配置ID')
    is_delete = Column(Integer, nullable=False, comment='是否删除')
    message_id = Column(String(36), nullable=False, comment='消息id')
    message_type = Column(String(8), nullable=False, comment='消息类型')

    def to_dict(self):
        return {
            'id': self.id if self.id is not None else "",
            'query': self.query if self.query is not None else "",
            'execute_datas': self.execute_datas if self.execute_datas is not None else "",
            'conversation_id': self.conversation_id if self.conversation_id is not None else "",
            'create_at': self.create_at.strftime('%Y-%m-%d %H:%M:%S') if self.create_at else "",
            'update_at': self.update_at.strftime('%Y-%m-%d %H:%M:%S') if self.update_at else "",
            'status': self.status if self.status is not None else 10,
            'user_id': self.user_id if self.user_id is not None else "",
            'data_conf_id': self.data_conf_id if self.data_conf_id is not None else "",
            # 'is_delete': self.is_delete if self.is_delete is not None else 10,
            'message_id': self.message_id if self.message_id is not None else "",
            'message_type': self.message_type if self.message_type is not None else "",
        }
    
    @classmethod
    def get_by_conversation_id(cls, conversation_id: str, page=1, per_page=50):
        """根据会话ID获取记录列表，支持分页"""
        # 先查询总数
        total = db.session.query(db.func.count(cls.id)).filter(cls.conversation_id == conversation_id, cls.is_delete != 1).scalar()
        
        # 计算偏移量
        offset = (page - 1) * per_page
        
        # 查询记录，按创建时间降序排列（最新的在前）
        records = db.session.query(cls).filter(
            cls.conversation_id == conversation_id, cls.is_delete != 1
        ).order_by(cls.create_at.desc()).offset(offset).limit(per_page).all()
        return records, total, page, per_page